import mysql.connector
from mysql.connector import Error
from aip import AipOcr
import json
from datetime import datetime

# 百度 OCR API 信息
APP_ID = '117941306'
API_KEY = 'gwKjPIekjP1CLHYXPJKBb5uX'
SECRET_KEY = 'vITzUDjsnbAddyJYfwryysYnvuQ0otZV'

# 初始化 AipOcr 对象
client = AipOcr(APP_ID, API_KEY, SECRET_KEY)

# 数据库连接信息
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'fapiao'
}


def get_file_content(file_path):
    """
    读取文件内容
    :param file_path: 文件路径
    :return: 文件的二进制内容
    """
    try:
        with open(file_path, 'rb') as fp:
            return fp.read()
    except FileNotFoundError:
        print(f"未找到文件: {file_path}")
        return None


def recognize_invoice(image_path):
    """
    识别发票
    :param image_path: 图片路径
    :return: 识别结果
    """
    image = get_file_content(image_path)
    if image is None:
        return None
    try:
        res_image = client.vatInvoice(image)
        return res_image
    except Exception as e:
        print(f"识别过程中出现异常: {e}")
        return None


def save_to_database(result, image_url):
    """
    将识别结果保存到数据库
    :param result: 识别结果
    :param image_url: 发票图片的 URL 地址
    """
    if result is None or 'words_result' not in result:
        return

    words_result = result['words_result']

    # 日期格式转换
    issue_date_str = words_result.get('InvoiceDate', '')
    try:
        issue_date = datetime.strptime(issue_date_str, '%Y年%m月%d日').strftime('%Y-%m-%d')
    except ValueError:
        issue_date = ''

    # 发票公共信息
    common_info = {
        'invoice_image_url': image_url,
        'invoice_name': words_result.get('InvoiceTypeOrg', ''),  # 对应 InvoiceTypeOrg
        'invoice_code': words_result.get('InvoiceCode', ''),  # 对应 InvoiceCode
        'invoice_number': words_result.get('InvoiceNum', ''),  # 对应 InvoiceNum
        'issue_date': issue_date,
        'purchaser_name': words_result.get('PurchaserName', ''),
        'seller_name': words_result.get('SellerName', ''),
    }

    # 获取货物信息列表
    commodity_names = words_result.get('CommodityName', [])
    commodity_nums = words_result.get('CommodityNum', [])
    commodity_prices = words_result.get('CommodityPrice', [])
    commodity_amounts = words_result.get('CommodityAmount', [])
    commodity_tax_rates = words_result.get('CommodityTaxRate', [])
    commodity_taxes = words_result.get('CommodityTax', [])
    commodity_units = words_result.get('CommodityUnit', [])

    # 打印列表长度和内容用于调试
    print(f"CommodityName 长度: {len(commodity_names)}, 内容: {commodity_names}")
    print(f"CommodityNum 长度: {len(commodity_nums)}, 内容: {commodity_nums}")
    print(f"CommodityPrice 长度: {len(commodity_prices)}, 内容: {commodity_prices}")
    print(f"CommodityAmount 长度: {len(commodity_amounts)}, 内容: {commodity_amounts}")
    print(f"CommodityTaxRate 长度: {len(commodity_tax_rates)}, 内容: {commodity_tax_rates}")
    print(f"CommodityTax 长度: {len(commodity_taxes)}, 内容: {commodity_taxes}")
    print(f"CommodityUnit 长度: {len(commodity_units)}, 内容: {commodity_units}")

    # 确保各列表长度一致
    max_length = max(len(commodity_names), len(commodity_nums), len(commodity_prices),
                     len(commodity_amounts), len(commodity_tax_rates), len(commodity_taxes), len(commodity_units))

    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        cursor = connection.cursor()

        for i in range(max_length):
            # 处理每行货物信息
            goods_name = commodity_names[i]['word'] if i < len(commodity_names) else ''
            specification_model = commodity_units[i].get('word', '') if i < len(commodity_units) else ''
            unit = commodity_nums[i]['word'] if i < len(commodity_nums) else ''

            try:
                quantity = float(commodity_prices[i]['word']) if i < len(commodity_prices) else 0.0
            except (ValueError, IndexError):
                quantity = 0.0

            try:
                amount = float(commodity_amounts[i]['word']) if i < len(commodity_amounts) else 0.0
            except (ValueError, IndexError):
                amount = 0.0

            # 处理税率
            try:
                tax_rate_str = commodity_tax_rates[i]['word'].replace('%', '') if i < len(commodity_tax_rates) else '0'
                tax_rate = float(tax_rate_str) / 100
            except (ValueError, IndexError):
                tax_rate = 0.0

            try:
                tax_amount = float(commodity_taxes[i]['word']) if i < len(commodity_taxes) else 0.0
            except (ValueError, IndexError):
                tax_amount = 0.0

            data = {
                **common_info,
                'goods_name': goods_name,
                'specification_model': specification_model,
                'unit': unit,
                'quantity': quantity,
                'amount': amount,
                'tax_rate': tax_rate,
                'tax_amount': tax_amount,
                'total_amount': float(words_result.get('TotalAmount', '0')),
                'total_tax_amount': float(words_result.get('TotalTax', '0')),
                'total_price_tax': float(words_result.get('AmountInFiguers', '0'))
            }

            insert_query = """
            INSERT INTO fapiao (invoice_image_url, invoice_name, invoice_code, invoice_number, issue_date,
                                purchaser_name, seller_name, goods_name, specification_model, unit,
                                quantity, amount, tax_rate, tax_amount,
                                total_amount, total_tax_amount, total_price_tax)
            VALUES (%(invoice_image_url)s, %(invoice_name)s, %(invoice_code)s, %(invoice_number)s, %(issue_date)s,
                    %(purchaser_name)s, %(seller_name)s, %(goods_name)s, %(specification_model)s, %(unit)s,
                    %(quantity)s,  %(amount)s, %(tax_rate)s, %(tax_amount)s,
                    %(total_amount)s, %(total_tax_amount)s, %(total_price_tax)s)
            """
            try:
                cursor.execute(insert_query, data)
            except Error as insert_error:
                print(f"插入第 {i + 1} 行数据时出现错误: {insert_error}")
                print(f"插入的数据: {data}")

        connection.commit()
        print("数据插入成功")
    except Error as e:
        print(f"数据库连接或操作出现错误: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()